#!/bin/bash
if [ -n "$1" ] ; then
  data_str=$1
else
  data_str=`date -d "-1 days" +%F`
fi
SQL_TMP_EVENT_LOG_CLEANED="
DROP TABLE IF EXISTS jtp_app_tmp.tmp_event_log_cleaned;
CREATE TABLE jtp_app_tmp.tmp_event_log_cleaned AS
SELECT account,
       appid,
       appversion,
       carrier,
       deviceid,
       devicetype,
       eventid,
       ip,
       latitude,
       longitude,
       nettype,
       osname,
       osversion,
       properties,
       releasechannel,
       resolution,
       sessionid,
       \`timestamp\`,
       dt
FROM jtp_app_ods.ods_event_app_log
where dt='${data_str}'
  AND deviceid IS NOT NULL AND length(deviceid) !=0
  AND eventid IS NOT NULL AND length(eventid) !=0
  AND sessionid IS NOT NULL AND length(sessionid) !=0
  AND properties IS NOT NULL  AND size(properties) !=0;
"
SQL_TMP_EVENT_LOG_SESSION="
DROP TABLE  IF EXISTS jtp_app_tmp.tmp_event_log_session;
CREATE TABLE jtp_app_tmp.tmp_event_log_session AS
WITH tmp1 AS (SELECT *,
                     \`timestamp\` - lag(\`timestamp\`, 1, 0L) over (PARTITION BY sessionid
                         ORDER BY \`timestamp\`) AS diff_ts
              FROM jtp_app_tmp.tmp_event_log_cleaned),
     tmp2 AS (SELECT *,
                     if(diff_ts / 1000 / 60 >= 10,
                        concat(sessionid, '_', diff_ts), null) AS strart_session_id
              FROM tmp1)
SELECT account
     , appid
     , appversion
     , carrier
     , deviceid
     , devicetype
     , eventid
     , ip
     , latitude
     , longitude
     , nettype
     , osname
     , osversion
     , properties
     , releasechannel
     , resolution
     , last_value(strart_session_id,TRUE) OVER (PARTITION BY sessionid ORDER BY \`timestamp\`) AS new_sessionid
     , \`timestamp\`
     , dt
FROM tmp2
;"
SQL_TMP_EVENT_LOG_AREA="
DROP FUNCTION IF EXISTS TOGeoHash;
CREATE FUNCTION TOGeoHash
    AS 'udf.ConvertGeoHash' USING JAR 'hdfs://node101:8020/warehouse/app_jars/zg6-jtp-log-1.0.jar';
DROP TABLE IF EXISTS jtp_app_tmp.tmp_event_log_area;
CREATE TABLE IF NOT EXISTS jtp_app_tmp.tmp_event_log_area AS
SELECT t1.*
     , t2.province
     , t2.city
     , t2.district
     , t2.street
FROM jtp_app_tmp.tmp_event_log_session t1
         LEFT JOIN jtp_app_dim.dim_md_areas t2
             ON TOGeoHash(t1.latitude, t1.longitude, 6) = t2.geo_hash ;
"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${SQL_TMP_EVENT_LOG_CLEANED}"
/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${SQL_TMP_EVENT_LOG_SESSION}"
/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${SQL_TMP_EVENT_LOG_AREA}"
